-- ===============================
-- Omni user group - January, 2022
-- ===============================
--
-- ===============================
-- Author: Scott Forstie
-- ===============================
--
-- ===============================
-- Db2 for i SQL Tutor: 
-- https://ibm.biz/Db2foriSQLTutor
-- https://www.ibm.com/support/pages/ibm-i-tutorials-demos-and-sql-examples-0
--
-- ===============================
-- Forstie Gists:
-- https://gist.github.com/forstie
--
-- ===============================
-- iSee Video blog series:
-- https://learn.common.org/products/ibm-isee-video-blog#tab-product_tab_overview
--

-- What should you do if you're running low on "Dad jokes"?
select *
  from json_table(
      QSYS2.HTTP_GET(
        'https://icanhazdadjoke.com/', '{"header":"Accept,application/json","sslTolerate":"true"}'),
      'lax $'
      columns(
        "joke" varchar(200) ccsid 1208
      )
    );
stop;

--
-- Lets function-ize it!
--
create or replace function coolstuff.threejokes ()
  returns table (
    joke varchar(200) for sbcs data
  )
  not deterministic
  no external action
  not fenced
  reads sql data
  system_time sensitive no
  set option dynusrprf = *user, usrprf = *user
  begin
    pipe (
        select "joke"
          from json_table(
              QSYS2.HTTP_GET(
                'https://icanhazdadjoke.com/',
                '{"header":"Accept,application/json","sslTolerate":"true"}'),
              'lax $'
              columns(
                "joke" varchar(200) ccsid 1208
              )
            ));
    pipe (
        select "joke"
          from json_table(
              QSYS2.HTTP_GET(
                'https://icanhazdadjoke.com/',
                '{"header":"Accept,application/json","sslTolerate":"true"}'),
              'lax $'
              columns(
                "joke" varchar(200) ccsid 1208
              )
            ));
    pipe (
        select "joke"
          from json_table(
              QSYS2.HTTP_GET(
                'https://icanhazdadjoke.com/',
                '{"header":"Accept,application/json","sslTolerate":"true"}'),
              'lax $'
              columns(
                "joke" varchar(200) ccsid 1208
              )
            ));
    return;
  end;

stop;

-- description: my demo udtf
select *
  from table (
      coolstuff.threejokes()
    );
stop;

--
-- Convert CYYMMDD to a date
--
create or replace variable coolstuff.cldate char(7);

set coolstuff.cldate = '2220118';

select
  date(timestamp_format(
    case
      when substr(coolstuff.cldate, 1, 1) = '0'
        then overlay(coolstuff.cldate, '19', 1, 1)
      else overlay(coolstuff.cldate, '20', 1, 1)
    end, 'YYYYMMDD')) as its_a_date
  from sysibm.sysdummy1;
stop;

--
-- Find SQL Indexes that have LVLCHK(*NO)
-- 
select SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME
  from QSYS2.SYSFILES
  where SYSTEM_TABLE_SCHEMA = 'LIBMHV' and
        sql_object_type = 'INDEX' and
        LEVEL_CHECK = 'NO';
--
-- Change those indexes to LVLCHK(*YES)
--
-- Executing commands in the select list!
-- 
select SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME,
       qsys2.qcmdexc(
         'CHGLF FILE(' concat SYSTEM_TABLE_SCHEMA concat '/' concat SYSTEM_TABLE_NAME concat
           ') LVLCHK(*YES)') as chglf_result
  from QSYS2.SYSFILES
  where SYSTEM_TABLE_SCHEMA = 'LIBMHV' and
        sql_object_type = 'INDEX' and
        LEVEL_CHECK = 'NO';
stop;

--
-- description: Compute the ALLOCATE(n) value, based upon the 80/20 rule
--              (80% of the time, the length of the column data would be less than or equal to n)
-- minvrm: V7R3M0
--
create or replace function systools.compute_allocate (
      p_schema_name varchar(128) for sbcs data, 
      p_table_name  varchar(128) for sbcs data, 
      p_column_name  varchar(128) for sbcs data, 
      allocate_percentage decimal(3,2) 
  )
  returns table (
    ideal_allocate_length bigint, 
    percentage_of_rows_that_fit_into_the_allocated_length decimal(5,4)
  )
  not deterministic
  no external action
  not fenced
  modifies sql data
  called on null input
  system_time sensitive no
  set option dynusrprf = *user, usrprf = *user
  BEGIN
   DECLARE local_sqlcode INTEGER;
   DECLARE local_sqlstate CHAR(5) for sbcs data;
   DECLARE v_message_text VARCHAR(70) for sbcs data;
   DECLARE v_percent decimal(5,4);
   DECLARE v_data_length bigint;
   declare not_found condition for '02000';
   declare at_end integer default 0;
   
   DECLARE allocate_analysis_cursor_stmttext varchar(10000) for sbcs data;
   DECLARE allocate_analysis_cursor CURSOR FOR allocate_analysis_cursor_statement;
      
   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
   BEGIN
      GET DIAGNOSTICS CONDITION 1
            local_sqlcode = db2_returned_sqlcode, local_sqlstate = returned_sqlstate;
      SET v_message_text = 'systools.compute_allocate() failed with: ' CONCAT local_sqlcode
               CONCAT '  AND ' CONCAT local_sqlstate;
      SIGNAL SQLSTATE 'QPC01' SET MESSAGE_TEXT = v_message_text;
   END;
   
   set allocate_analysis_cursor_stmttext = 
    'with column_lengths (data_length, data_length_count) as (
       select
         case when length(' concat qsys2.delimit_name(p_column_name) concat ') is null then 0
              else length(' concat qsys2.delimit_name(p_column_name) concat ')
         end, count(*)
         from ' concat qsys2.delimit_name(p_schema_name) concat '.' 
         concat qsys2.delimit_name(p_table_name) concat 
         ' group by
           case
             when length(' concat qsys2.delimit_name(p_column_name) concat ') is null then 0
             else length(' concat qsys2.delimit_name(p_column_name) concat ')
           end
     ),
     column_lengths_with_ratio (data_length, data_length_count, ratio) as (
       select data_length, data_length_count, ratio_to_report (data_length_count) over (
              ) as ratio
         from column_lengths
     ),
     column_lengths_with_ratio_and_rolling_sum (data_length, data_length_count, ratio,
         rolling_ratio_sum) as (
       select data_length, data_length_count, ratio, sum(ratio) over (
                order by data_length asc
              )
         from column_lengths_with_ratio
     )
  (select data_length, dec(rolling_ratio_sum, 5, 4) as percent
      from column_lengths_with_ratio_and_rolling_sum
      where rolling_ratio_sum < ?
    order by rolling_ratio_sum desc
    limit 1)
  union all
  (select data_length, dec(rolling_ratio_sum, 5, 4) as percent
      from column_lengths_with_ratio_and_rolling_sum
      where rolling_ratio_sum > ?
    order by rolling_ratio_sum asc
    limit 1)';
    
    PREPARE allocate_analysis_cursor_statement from allocate_analysis_cursor_stmttext;
    OPEN allocate_analysis_cursor using allocate_percentage, allocate_percentage;
    -- return the ALLOCATE value that would be closest (but below) the percent target
    FETCH FROM allocate_analysis_cursor into v_data_length, v_percent;
    PIPE( v_data_length, v_percent );
    
    -- return the ALLOCATE value that would be closest (but above) the percent target
    FETCH FROM allocate_analysis_cursor into v_data_length, v_percent;
    PIPE( v_data_length, v_percent );

    CLOSE allocate_analysis_cursor;
    RETURN;
END;
stop;

--
-- Having trouble sleeping?

-- Purpose: Delay job for fractions of a second
--
--
cl: addlible QSYSINC;
cl: crtsrcpf qtemp/qcsrc;
cl: addpfm file(qtemp/qcsrc) mbr(usleep);
--
-- The usleep() function suspends a thread for the number of microseconds specified by the of useconds parameter.
--                                                           ============
-- 1 sec == 1,000 milisec == 1,000,000 microsec == 1,000,000,000 nanosec
--
insert into qtemp.qcsrc values
 (1,010101,'{'),                                                  
 (2,010101,'#include "unistd.h"'),      
 (3,010101,'while(USLEEP.SLEEPUS>1000000)'),
 (4,010101,'  { usleep(1000000); USLEEP.SLEEPUS-=1000000; }'),  
 (5,010101,'usleep(USLEEP.SLEEPUS);'),                                     
 (6,010101,'}');

CREATE OR REPLACE PROCEDURE systools.usleep(sleepus int)
program type sub
-- Use this SET OPTION if you see a compile failure [SQL7032]
-- SET OPTION dbgview = *source, output=*PRINT
   BEGIN
      INCLUDE qtemp / qcsrc(usleep);
   END;

-- Sleep for 1 microsecond
CALL systools.usleep(1);
  
-- which would you prefer?
select LIBRARY_LIST_COUNT, LIBRARY_LIST
  from QSYS2.JOB_DESCRIPTION_INFO
  where jobd = 'DAWNUSER';
  
stop;

select b.*
  from QSYS2.JOB_DESCRIPTION_INFO, table (
         SYSTOOLS.GET_LIB_NAMES(JOBD_LIBL     => LIBRARY_LIST, 
                                JOBD_LIBL_CNT => LIBRARY_LIST_COUNT)
       ) b
  where jobd = 'DAWNUSER';

stop;

-- Database fragmentation
select *
  from qsys2.syslimits_basic
  where sizing_name like '%DELETED%'
  order by current_value desc;
stop;

select TABLE_NAME, TABLE_PARTITION, NUMBER_ROWS, NUMBER_DELETED_ROWS
  from qsys2.syspartitionstat
  where table_schema = 'QPFRHIST'
  order by overflow desc;
stop;

-- Review the distribution of deleted records
SELECT 1000000 - COUNT(*) AS DELETEDCNT
   FROM QPFRHIST.QAPMHDJOBM A
   GROUP BY BIGINT(RRN(A) / 1000000)
   ORDER BY BIGINT(RRN(A) / 1000000);
   
create or replace alias qtemp.fragm for QPFRHIST.QAPMHDJOBM(QPFRHIST);

SELECT 1000000 - COUNT(*) AS DELETEDCNT
   FROM qtemp.fragm A
   GROUP BY BIGINT(RRN(A) / 1000000)
   ORDER BY BIGINT(RRN(A) / 1000000);
stop;

-- hmmm
select *
  from qsys2.db_transaction_info;
stop; 
-- execute this in another session
update toystore2.sales set sales = sales + 1 limit 5;

-- To setup (or reset) this test:
        drop schema toystore  cascade;
        call qsys.create_sql_sample('TOYSTORE2');

-- Active transactions with pending database changes
select *
  from qsys2.db_transaction_info
  where local_record_changes_pending = 'YES' or
        local_object_changes_pending = 'YES'; 
stop;
-- One step after another
with pendingwork (jn) as (
    select job_name
      from qsys2.db_transaction_info
      where local_record_changes_pending = 'YES' or
            local_object_changes_pending = 'YES'
  )
  select *
    from pendingwork;
stop;
-- LaTeRal ... (Left To Right)
with pendingwork (jn) as (
    select job_name
      from qsys2.db_transaction_info
      where local_record_changes_pending = 'YES' or
            local_object_changes_pending = 'YES'
  )
  select jn, jl.*
    from pendingwork, lateral (select * from table(qsys2.job_lock_info(jn))) jl;
stop;
-- predicate
with pendingwork (jn) as (
    select job_name
      from qsys2.db_transaction_info
      where local_record_changes_pending = 'YES' or
            local_object_changes_pending = 'YES'
  )
  select jn, jl.*
    from pendingwork, lateral (select * from table(qsys2.job_lock_info(jn))) jl
    where object_library not like 'Q%';
stop;
-- Shazam!
with pendingwork (jn) as (
    select job_name
      from qsys2.db_transaction_info
      where local_record_changes_pending = 'YES' or
            local_object_changes_pending = 'YES'
  )
  select jn, object_library as lib, object_name as file, rrn, ol.lock_state
    from pendingwork, lateral (
           select *
             from table (
                 qsys2.job_lock_info(jn)
               )
         ) jl, lateral (
           select *
             from qsys2.record_lock_info
             where table_schema = object_library and
                   table_name = object_name and
                   object_attribute = 'PF'
         ) ol
    where object_library not like 'Q%';
stop;      

-- Read an IFS stream file? Ubetcha
select line_number, line
  from table (
      qsys2.ifs_read(
        path_name => '/usr/local/install.log', 
        end_of_line => 'ANY',
        maximum_line_length => default, 
        ignore_errors => 'NO')
    );  
    
stop;
-- IBM® i NetServer shares
select server_share_name, path_name, permissions 
  from qsys2.server_share_info
  where share_type = 'FILE';

-- IBM® i NetServer shares - IFS security detail
with shares (name, pn, perm) as (
    select server_share_name, path_name, permissions
      from qsys2.server_share_info
      where share_type = 'FILE'
  )
  select name, pn, perm, authorization_name as username,
         data_authority as actual_data_authority
    from shares, lateral (
           select *
             from table (
                 qsys2.ifs_object_privileges(path_name => pn)
               )
         );
stop;

-- Data queues and Dairy Queen.... two great DQs
drop schema TheQueen cascade;
create schema TheQueen;
cl:CRTDTAQ DTAQ(TheQueen/OrderDQ) MAXLEN(100) SEQ(*KEYED) KEYLEN(3);
call qsys2.send_data_queue(message_data       => 'Floyd - Dilly Bar',
                           data_queue         => 'ORDERDQ', 
                           data_queue_library => 'THEQUEEN',
                           key_data           => '010');
call qsys2.send_data_queue(message_data       => 'Mike - Mister Misty',
                           data_queue         => 'ORDERDQ', 
                           data_queue_library => 'THEQUEEN',
                           key_data           => '020');
call qsys2.send_data_queue(message_data       => 'Scott - Strawberry Sundae',
                           data_queue         => 'ORDERDQ', 
                           data_queue_library => 'THEQUEEN',
                           key_data           => '030');
call qsys2.send_data_queue(message_data       => 'Scott - Pineapple Shake',
                           data_queue         => 'ORDERDQ', 
                           data_queue_library => 'THEQUEEN',
                           key_data           => '030');
stop;

-- Search what's on the DQ
select message_data, key_data from table
     (qsys2.data_queue_entries('ORDERDQ', 'THEQUEEN', 
                               selection_type => 'KEY',
                               key_data       => '030',
                               key_order      => 'EQ'));
stop;

-- Order fulfilled!
select message_data, message_data_utf8, message_data_binary, key_data, sender_job_name, sender_current_user
  from table (
      qsys2.receive_data_queue(
        data_queue => 'ORDERDQ', data_queue_library => 'THEQUEEN', 
        remove => 'YES',
        wait_time => 0, 
        key_data => '030', 
        key_order => 'EQ')
    );
stop;

-- What remains on the queue?
select * from table
     (qsys2.data_queue_entries('ORDERDQ', 'THEQUEEN', 
                               selection_type => 'KEY',
                               key_data       => '030',
                               key_order      => 'LE'));
                               
stop;

--
-- Which SQL programs or services have a mismatch between user profile and dynamic user profile (full)
--
select qsys2.delimit_name(system_program_schema) as lib, 
       qsys2.delimit_name(system_program_name) as pgm, 
       program_type as type
  from qsys2.sysprogramstat
  where system_program_schema = 'SCOTTF'
        and dynamic_user_profile = '*USER' 
        and program_type in ('*PGM', '*SRVPGM')
        and ((user_profile = '*OWNER')
          or (user_profile = '*NAMING'
            and naming = '*SQL'))
  order by program_name;
stop;

-- *NAMING
-- The user profile is determined by the naming convention. 
-- If the naming convention is *SQL, USRPRF(*OWNER) is used. <==== !!!!!
-- If the naming convention is *SYS, USRPRF(*USER) is used.

select dynamic_user_profile, user_profile, naming, s.*
  from qsys2.sysprogramstat s
  where system_program_schema = 'SCOTTF' and
        program_name = 'ADD_THEM';
stop;

-- DBE in a box?
CALL COOLSTUFF.BUILD_VIEWS_OVER_PHYSICALS(
  P_TABLE_SCHEMA => 'COOLSTUFF'   /* IN VARCHAR(128)    */
);


-- Features Used : This Gist uses QSYS2.SYSFILES, QSYS2.SYSCOLUMNS2, dynamic SQL, and SQL PL
--
-- Function - The request was, I don't have a DBE.. I don't have SQL Views... how do I get started with
--            shifting users and applications away from directly consuming the physical files?
--
--

create or replace function coolstuff.build_view_statement(p_table_schema varchar(128) for sbcs data, 
                                                          p_table_name varchar(128) for sbcs data,
                                                          p_view_name varchar(128) for sbcs data,
                                                          p_view_system_name varchar(128) for sbcs data default null)
returns clob(1M) for sbcs data 
no external action
modifies sql data 
not fenced 
not deterministic
set option usrprf = *USER, dynusrprf = *user, commit = *none
begin
  declare v_column_name varchar(128) for sbcs data;
  declare v_system_column_name varchar(10) for sbcs data;
  declare v_rcdfmt varchar(10) for sbcs data;
  declare final_column_text clob(100K) for sbcs data default '';
  declare final_select_text clob(100K) for sbcs data default ' select ';
  declare final_create_view_text clob(100K) for sbcs data default ' create view ';

  declare not_found condition for '02000';
  declare at_end integer default 0;
  declare lc integer default 0;
  declare local_sqlcode integer;
  declare local_sqlstate char(5);
  declare v_message_text varchar(70) for sbcs data;
  
  declare column_cursor_stmt_text varchar(2000) for sbcs data default 
  'select
     qsys2.delimit_name(column_name), 
     qsys2.delimit_name(system_column_name) 
   from qsys2.syscolumns2 c
     where table_schema = ? and table_name = ? 
     AND HIDDEN = ''N'' 
   order by ordinal_position';   -- Don't include hidden columns
  declare column_cursor cursor for column_cursor_stmt;

  declare continue handler for sqlexception 
    begin
    get diagnostics condition 1 local_sqlcode = db2_returned_sqlcode, local_sqlstate = returned_sqlstate;
    set v_message_text = 'coolstuff.view_column_names() failed with: ' concat local_sqlcode concat ' AND ' concat local_sqlstate;
    signal sqlstate 'QZY01' set message_text = v_message_text;
    set at_end = 1;
  end;
  declare continue handler for not_found set at_end = 1;

  prepare column_cursor_stmt from column_cursor_stmt_text;
  open column_cursor using p_table_schema, p_table_name;
  set at_end = 0;
  fetch from column_cursor into v_column_name, v_system_column_name; 
  
  while (at_end = 0) do   
    set lc = lc + 1;
    if (lc > 1) then 
      set final_column_text = final_column_text concat ', ';
      set final_select_text = final_select_text concat ', ';
    end if;
    set final_select_text = final_select_text concat v_column_name;
    if (v_column_name = v_system_column_name) then
      set final_column_text = final_column_text concat v_column_name;
    else
      set final_column_text = final_column_text concat v_column_name concat ' FOR COLUMN ' concat v_system_column_name;
    end if; 
    fetch from column_cursor into v_column_name, v_system_column_name;   
  end while;   
  close column_cursor;
  
  set final_select_text = final_select_text concat ' from ' concat qsys2.delimit_name(p_table_schema) concat '.' concat qsys2.delimit_name(p_table_name);
  
  set final_create_view_text = final_create_view_text concat qsys2.delimit_name(p_table_schema) concat '.' concat qsys2.delimit_name(p_view_name) 
     concat case when p_view_system_name is not null then ' for system name ' concat qsys2.delimit_name(p_view_system_name) else '' end 
     concat ' ( ' concat final_column_text concat ' ) as ( '  concat final_select_text concat ' ) ';

  select format_name into v_rcdfmt from qsys2.sysfiles 
    where table_schema = p_table_schema and
          table_name   = p_table_name;
  if (v_rcdfmt is not null) then 
    set final_create_view_text = final_create_view_text concat ' rcdfmt ' concat v_rcdfmt;
  end if;
  return final_create_view_text;
end;       
stop;

values coolstuff.build_view_statement(
    P_TABLE_SCHEMA     => 'TOYSTORE', 
    P_TABLE_NAME       => 'SALES', 
    P_VIEW_NAME        => 'SALESV',
    P_VIEW_SYSTEM_NAME => default);
    
create or replace variable scottf.seeit clob(1m);
stop;

create or replace procedure coolstuff.build_views_over_physicals(p_table_schema varchar(128) for sbcs data)
external action
modifies sql data 
dynamic result sets 1 
set option usrprf = *USER, dynusrprf = *user, commit = *none
begin
  declare v_table_name varchar(128) for sbcs data;
  declare v_view_name varchar(128) for sbcs data;
  declare v_system_table_name varchar(10) for sbcs data; 
  declare v_owner varchar(10) for sbcs data;
  declare v_object_audit varchar(10) for sbcs data;
  
  declare view_library_name varchar(10) for sbcs data;
  declare view_object_name varchar(10) for sbcs data;
  declare create_view_text clob(1M) for sbcs data;
  declare transfer_ownership_text varchar(500) for sbcs data;
  declare chgobjaud_cmd varchar(500) for sbcs data;
  declare grtobjaud_cmd varchar(500) for sbcs data;
  
  declare not_found condition for '02000';
  declare at_end integer default 0;
  declare lc integer default 0;
  declare local_sqlcode integer;
  declare local_sqlstate char(5);
  declare v_message_text varchar(100) for sbcs data; 
  
  declare physical_files_stmt_text varchar(2000) for sbcs data default 
  'select table_name, system_table_name, file_owner
     from qsys2.sysfiles
     where table_schema = ? and
           native_type  = ''PHYSICAL'' and
           file_type    = ''DATA''     and
           program_described = ''NO'' and
           number_members = 1';
  declare physical_files_cursor cursor for physical_files_cursor_stmt;
  declare views_created_cursor cursor for select * from session.views_created;

  declare continue handler for sqlexception 
    begin
    get diagnostics condition 1 local_sqlcode = db2_returned_sqlcode, local_sqlstate = returned_sqlstate, v_message_text = message_text;
    --set v_message_text = 'coolstuff.build_views_over_physicals() failed with: ' concat local_sqlcode concat ' AND ' concat local_sqlstate;
    --signal sqlstate 'QZV01' set message_text = v_message_text;
    --set at_end = 1;
  end;
  declare continue handler for not_found set at_end = 1;
  
  -- Populate the list of objects that were created
  declare global temporary table session.views_created (
        table_schema            varchar(258) for sbcs data,
        table_name              varchar(258) for sbcs data,
        view_schema             varchar(258) for sbcs data,
        view_name               varchar(258) for sbcs data,
        view_library_name       char(10) for sbcs data,
        view_system_object_name char(10) for sbcs data,
        view_sqlcode            integer, 
        view_sqlstate           char(5) for sbcs data,
        view_error_text         varchar(1000) for sbcs data,
        create_view_stmt        clob(1M) for sbcs data
      )
    with replace;

  prepare physical_files_cursor_stmt from physical_files_stmt_text;
  open physical_files_cursor using p_table_schema;
  set at_end = 0;
  fetch from physical_files_cursor into v_table_name, v_system_table_name, v_owner; 

  while (at_end = 0) do   
    set lc = lc + 1;
    set v_view_name = v_table_name concat 'V';
    set create_view_text = 
         coolstuff.build_view_statement(
           P_TABLE_SCHEMA     => p_table_schema, 
           P_TABLE_NAME       => v_table_name, 
           P_VIEW_NAME        => v_view_name,
           P_VIEW_SYSTEM_NAME => default);

    set local_sqlcode = 0;
    set local_sqlstate = '';
     
    begin 
      declare continue handler for sqlexception 
        begin
          get diagnostics condition 1 local_sqlcode = db2_returned_sqlcode, local_sqlstate = returned_sqlstate;
        end;
      -- ================================================================================================
      -- Create the view
      -- ================================================================================================
      execute immediate create_view_text;
    end;
    
    if (local_sqlcode = 0) then 
      -- ================================================================================================
      -- Transfer the ownership of the new view to be owned by the owner of the base physical file
      -- ================================================================================================
      set transfer_ownership_text = 'transfer ownership of view ' concat qsys2.delimit_name(p_table_schema) concat '.' concat qsys2.delimit_name(v_view_name) concat ' to user ' concat qsys2.delimit_name(v_owner);
      execute immediate transfer_ownership_text;
      select objname, objlib into view_object_name, view_library_name
        from table(qsys2.object_statistics(p_table_schema, '*FILE', v_view_name)); 
      
      -- ================================================================================================
      -- Inherit object auditing configuration of the base physical file, if applicable
      -- ================================================================================================
      select OBJECT_AUDIT into v_object_audit
        from table(qsys2.object_statistics(p_table_schema, '*FILE', v_table_name));
      if (v_object_audit is not null) and (v_object_audit <> '*NONE') then 
        set chgobjaud_cmd = 'QSYS/CHGOBJAUD OBJ(' concat qsys2.delimit_name(view_library_name) concat '/' concat qsys2.delimit_name(view_object_name) concat ') OBJTYPE(*FILE) OBJAUD(' concat v_object_audit concat ')';
      end if;
    
      -- ================================================================================================
      -- Inherit the authorization configuration of the base physical file
      -- ================================================================================================
      set grtobjaud_cmd = 'QSYS/GRTOBJAUT OBJ(' concat qsys2.delimit_name(view_library_name) concat '/' concat qsys2.delimit_name(view_object_name) concat 
                                            ') OBJTYPE(*FILE) REFOBJ(' concat qsys2.delimit_name(view_library_name) concat '/' concat qsys2.delimit_name(v_system_table_name) concat ')';
    end if;
    
    -- ================================================================================================
    -- Log the result
    -- ================================================================================================
    insert into session.views_created values(p_table_schema, v_table_name, p_table_schema, v_view_name, view_library_name, view_object_name, 
                                             local_sqlcode, local_sqlstate, v_message_text, create_view_text);
     
    fetch from physical_files_cursor into v_table_name, v_system_table_name, v_owner; 
  end while;   
  close physical_files_cursor;
  open views_created_cursor;
end;       
stop;
-- ================================================================================================
--
-- To invoke, pass in the name of the schema:
-- 
-- ================================================================================================
call coolstuff.build_views_over_physicals(p_table_schema => 'TOYSTORE'); 
stop;

-- ================================================================================================
--
-- If you lose the result set window, query this to get it back: 
-- 
-- ================================================================================================
select * from session.views_created;